package com.mp.approvalreq.dao.impl;

import com.alibaba.fastjson.JSONObject;
import com.mp.approvalreq.dao.RequestDao;
import com.mp.approvalreq.entity.Request;
import com.mp.approvalreq.entity.adjust.VersionInfo;
import com.mp.approvalreq.util.Formatter;
import com.plusmoney.util.SQLLogger;
import com.plusmoney.util.Utils;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.*;

public class RequestDaoImpl extends AbstractDao implements RequestDao {

    private static final Logger logger = LoggerFactory.getLogger(RequestDaoImpl.class);

    private Connection connection;

    public RequestDaoImpl(Connection connection) {
        this.connection = connection;
    }

    private PreparedStatement getPreparedStatement(String sql) throws SQLException {
        return connection.prepareStatement(sql);
    }

    @Override
    String getQuerySql(String condition) {
        final String query = "select Frequest_id, Frequest_name, Frequest_templet, Fapplicant, Fcreate_time,"
                + " Ffinished, Fversion, Fouter_position, Frequest_ancestor_id, Frequest_content_last_total_second,"
                + " Fnatural_content_time_last, Fall_judger, Fwho_confirm, Fmanual_ending_time, Frela_people,"
                + " Fchecked_time, Ftotal_money, Frequest_step, Ffinally_confirmed_time, Ffinally_cc_time,"
                + " Fcc, Fcc_ciid, Fapplicant_ciid, Fwho_the_next, Fwho_the_next_ciid,"
                + " Flatest_approved_time, Frequest_template_type, Fdisable_time, Fcompany_id, Ffinancial_status"
                + " from request_flow";
        return String.format("%s %s", query, condition);
    }

    @Override
    Request getObject(ResultSet rs) throws SQLException {
        Request request = new Request();
        request.setRequestDkey(rs.getInt(1));
        request.setRequestName(rs.getString(2));
        request.setTemplateId(rs.getInt(3));
        request.setApplicant(rs.getInt(4));
        request.setCreateTimestamp(rs.getTimestamp(5));
        request.setRequestTime(getTimeStr(rs.getTimestamp(5)));
        request.setCreateTime(getTimeStr(rs.getTimestamp(5)));

        request.setFinished(rs.getInt(6));
        request.setVersion(rs.getInt(7));
        request.setOuterPosition(rs.getString(8));
        request.setRequestAncestorId(rs.getInt(9));
        request.setRequestContentLastTotalSecond(rs.getString(10));

        request.setNaturalContentTimeLast(rs.getString(11));
        request.setAllJudger(rs.getString(12));
        request.setWhoConfirm(rs.getString(13));
        request.setManualEndingTimeStr(getTimeStr(rs.getTimestamp(14)));
        request.setManualEndingTime(rs.getTimestamp(14));
        request.setRelaPeople(rs.getString(15));

        request.setCheckedTime(rs.getString(16));
        request.setTotalMoney(rs.getString(17));
        request.setRequestStep(rs.getInt(18));
        request.setFinallyConfirmedTime(getTimeStr(rs.getTimestamp(19)));
        request.setFinallyCcTime(getTimeStr(rs.getTimestamp(20)));

        request.setCc(rs.getString(21));
        request.setCcCiid(rs.getString(22));
        request.setApplicantCiid(rs.getInt(23));
        request.setWhoTheNext(rs.getString(24));
        request.setWhoTheNextCiid(rs.getString(25));

        request.setLatestApprovedTime(getTimeStr(rs.getTimestamp(26)));
        request.setDefaultType(rs.getInt(27));
        request.setDisableTime(getTimeStr(rs.getTimestamp(28)));
        request.setCompanyId(rs.getInt(29));
        request.setFinancialStatus(rs.getInt(30));
        if (Utils.isNotNull(request.getManualEndingTime())) {
            request.setIsLeavePause(1);
        }
        return request;
    }

    private String getDetailQuerySql(String condition) {
        final String query = "select Frequest_id, Frequest_name, Frequest_templet, Frequest_step, Fcompany_id,"
                + " Fapplicant, Fapplicant_ciid, Fcreate_time, Fchecked_time, Fcall_back_time,"
                + " Fdisable_time, Fcontent_ids, Fcontent_nums, Ffinished, Fversion,"
                + " Fouter_position, Fis_resubmit, Frefuse_chain, Frequest_template_type, Fmanual_ending_time,"
                + " Frequest_content_last, Ffinally_confirmed_time, Ffinally_cc_time, Frequest_ancestor_id, Frequest_content_last_total_second,"
                + " Fnatural_content_time_last, Fis_annual_leave, Flatest_approved_time, Fapproval_num, Ftotal_money,"
                + " Fgeneration, Fwho_confirm, Fwho_confirm_ciid, Fall_judger, Fall_judger_ciid,"
                + " Fwho_the_next, Fwho_the_next_ciid, Fwho_refused, Fwho_refused_ciid, Fcc,"
                + " Fcc_ciid, Frela_people, Frela_people_ciid, Foperator_id, Fproxy_contact_id,"
                + " Ffinancial_status"
                + " from request_flow";
        return String.format("%s %s", query, condition);

    }

    private Request getDetailObject(ResultSet rs) throws SQLException {
        Request request = new Request();
        request.setRequestDkey(rs.getInt(1));
        request.setRequestName(rs.getString(2));
        request.setTemplateId(rs.getInt(3));
        request.setRequestStep(rs.getInt(4));
        request.setCompanyId(rs.getInt(5));

        request.setApplicant(rs.getInt(6));
        request.setApplicantCiid(rs.getInt(7));
        // 前端使用两个"申请时间"字段
        request.setCreateTimestamp(rs.getTimestamp(8));
        request.setCreateTime(getTimeStr(rs.getTimestamp(8)));
        request.setRequestTime(getTimeStr(rs.getTimestamp(8)));
        request.setCheckedTime(rs.getString(9));
        request.setCallBackTime(getTimeStr(rs.getTimestamp(10)));

        request.setDisableTime(getTimeStr(rs.getTimestamp(11)));
        request.setContentIds(rs.getString(12));
        request.setContentNums(rs.getString(13));
        request.setFinished(rs.getInt(14));
        request.setVersion(rs.getInt(15));

        request.setOuterPosition(rs.getString(16));
        request.setIsResubmit(rs.getInt(17));
        request.setRefuseChain(rs.getString(18));
        request.setDefaultType(rs.getInt(19));
        request.setManualEndingTime(rs.getTimestamp(20));
        request.setManualEndingTimeStr(getTimeStr(rs.getTimestamp(20)));

        request.setRequestContentLastStr(rs.getString(21));
        request.setFinallyConfirmedTime(getTimeStr(rs.getTimestamp(22)));
        request.setFinallyCcTime(getTimeStr(rs.getTimestamp(23)));
        request.setRequestAncestorId(rs.getInt(24));
        request.setRequestContentLastTotalSecond(rs.getString(25));

        request.setNaturalContentTimeLast(rs.getString(26));
        request.setIsAnnualLeave(rs.getInt(27));
        request.setLatestApprovedTime(getTimeStr(rs.getTimestamp(28)));
        request.setApprovalNum(rs.getString(29));
        request.setTotalMoney(rs.getString(30));

        request.setGeneration(rs.getInt(31));
        request.setWhoConfirm(rs.getString(32));
        request.setWhoConfirmCiid(rs.getString(33));
        request.setAllJudger(rs.getString(34));
        request.setAllJudgerCiid(rs.getString(35));

        request.setWhoTheNext(rs.getString(36));
        request.setWhoTheNextCiid(rs.getString(37));
        request.setWhoRefused(rs.getString(38));
        request.setWhoRefusedCiid(rs.getString(39));
        request.setCc(rs.getString(40));

        request.setCcCiid(rs.getString(41));
        request.setRelaPeople(rs.getString(42));
        request.setRelaPeopleCiid(rs.getString(43));
        int operatorId = rs.getInt(44);
        if (operatorId > 0) {
            request.setOperatorId(operatorId);
        }
        request.setProxyContactId((Integer) rs.getObject(45));

        request.setFinancialStatus(rs.getInt(46));
        return request;
    }

    @Override
    public List<Request> selectRequestByIds(String requestIds) throws SQLException {
        List<Request> list = new ArrayList<>();
        if (StringUtils.isBlank(requestIds)) {
            return list;
        }
        String sql = getQuerySql("where Frequest_id in(" + requestIds + ")");

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            logger.info(sql);

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(getObject(rs));
                }
            }
        }
        return list;
    }

    @Override
    public List<Integer> selectRequestIdsByCompanyInfoId(Request query) throws SQLException {
        mergeStatus(query);
        List<Integer> list = new ArrayList<>();
        String sql = "select Frequest_id"
                + " from request_flow"
                + " where Fapplicant_ciid = ? and Ffinished in(" + query.getCurrentStatus() + ") and Fis_resubmit = 0";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, query.getApplicantCiid());
            logger.info(SQLLogger.logSQL(sql, query.getApplicantCiid()));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(rs.getInt(1));
                }
            }
        }
        return list;
    }

    @Override
    public List<Integer> selectRequestIdsByCompanyInfoId(Integer proxyContactId, Integer companyInfoId, String finishedSequence, String orderBy) throws SQLException {
        List<Integer> list = new ArrayList<>();
        if (StringUtils.isBlank(finishedSequence)) {
            return list;
        }
        String sql = "select Frequest_id"
                + " from request_flow"
                + " where (Fapplicant_ciid = ? or Fproxy_contact_id = ?) and Ffinished in(" + finishedSequence + ") and Fis_resubmit = 0";

        // 排序
        if (StringUtils.isEmpty(orderBy) || "0".equals(orderBy) || "1".equals(orderBy)) {
            sql += " order by Frequest_id desc";
        } else if ("2".equals(orderBy)) {
            sql += " order by Flatest_approved_time desc";
        } else if ("3".equals(orderBy)) {
            sql += " order by Ffinally_cc_time desc";
        }

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyInfoId);
            ps.setInt(2, proxyContactId);
            logger.info(SQLLogger.logSQL(sql, companyInfoId, proxyContactId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(rs.getInt(1));
                }
            }
        }
        return list;
    }

    @Override
    public List<Integer> selectRequestIdsByContactId(Integer contactId, String orderBy) throws SQLException {
        List<Integer> list = new ArrayList<>();
        String sql = "select Frequest_id"
                + " from request_flow"
                + " where Fapplicant = ? and Ffinished != -99 and Fis_resubmit = 0";

        // 排序
        if (StringUtils.isEmpty(orderBy) || "0".equals(orderBy) || "1".equals(orderBy)) {
            sql += " order by Frequest_id desc";
        } else if ("2".equals(orderBy)) {
            sql += " order by Flatest_approved_time desc";
        } else if ("3".equals(orderBy)) {
            sql += " order by Ffinally_cc_time desc";
        }

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, contactId);
            logger.info(SQLLogger.logSQL(sql, contactId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(rs.getInt(1));
                }
            }
        }
        return list;
    }

    @Override
    public List<Integer> selectUserNotApprovedRequestIds(Integer companyId, Integer companyInfoId) throws SQLException {
        List<Integer> list = new ArrayList<>();
        String sql = "select distinct(flow.request_id)"
                + " from sys_approval_flow flow"
                + " left join request_flow f on flow.request_id = f.Frequest_id"
                + " where flow.company_id = ? and flow.company_info_id = ? and flow.state = 1 and flow.is_approved_at is null"
                + " and f.Ffinished = 0 and f.Fis_resubmit = 0";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            ps.setInt(2, companyInfoId);
            logger.info(SQLLogger.logSQL(sql, companyId, companyInfoId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(rs.getInt(1));
                }
            }
        }
        return list;
    }

    @Override
    public void updateRequestUsersBatch(List<Request> requests, Integer companyId) throws SQLException {
        String sql = " update approval.request_flow set " +
                " Fwho_the_next = ?, Fwho_the_next_ciid = ?,  Fall_judger = ?, Fall_judger_ciid = ? " +
                " where Frequest_id = ? and Fcompany_id = ? and Ffinished = 0 ";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            for (Request request : requests) {
                ps.setString(1, request.getWhoTheNext());
                ps.setString(2, request.getWhoTheNextCiid());
                ps.setString(3, request.getAllJudger());
                ps.setString(4, request.getAllJudgerCiid());

                ps.setInt(5, request.getRequestDkey());
                ps.setInt(6, companyId);

                logger.info(SQLLogger.logSQL(sql, request.getWhoTheNext(), request.getWhoTheNextCiid(), request.getAllJudger(),
                        request.getAllJudgerCiid(), request.getRequestDkey(), companyId));

                ps.addBatch();
            }
            ps.executeBatch();
        }
    }

    @Override
    public void updateRequestInfo(Request request) throws SQLException {
        String sql = " update request_flow"
                + " set Ffinished = ?,  Fchecked_time = ?, Fwho_confirm = ?, Fwho_confirm_ciid = ?, Fwho_the_next = ?,"
                + " Fwho_the_next_ciid = ?, Fwho_refused = ?, Fwho_refused_ciid = ?, Ffinally_confirmed_time = ?,  Frequest_step = ?,"
                + " Flatest_approved_time = ?, Ffinancial_status = ?"
                + " where Frequest_id = ? and Fcompany_id = ? ";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, request.getFinished());
            ps.setString(2, request.getCheckedTime());
            ps.setString(3, request.getWhoConfirm());
            ps.setString(4, request.getWhoConfirmCiid());
            ps.setString(5, request.getWhoTheNext());

            ps.setString(6, request.getWhoTheNextCiid());
            ps.setString(7, request.getWhoRefused());
            ps.setString(8, request.getWhoRefusedCiid());
            ps.setString(9, request.getFinallyConfirmedTime());
            ps.setInt(10, request.getRequestStep());

            ps.setString(11, request.getLatestApprovedTime());
            ps.setInt(12, request.getFinancialStatus());
            ps.setInt(13, request.getRequestDkey());
            ps.setInt(14, request.getCompanyId());
            logger.info(SQLLogger.logSQL(sql, request.getFinished(), request.getCheckedTime(), request.getWhoConfirm(),
                    request.getWhoConfirmCiid(), request.getWhoTheNext(), request.getWhoTheNextCiid(), request.getWhoRefused(),
                    request.getWhoRefusedCiid(), request.getFinallyConfirmedTime(), request.getRequestStep(), request.getLatestApprovedTime(),
                    request.getFinancialStatus(), request.getRequestDkey(), request.getCompanyId()));

            ps.executeUpdate();
        }
    }

    @Override
    public Request selectRequestDetailById(Integer requestId) throws SQLException {
        String sql = getDetailQuerySql("where Frequest_id = ?");

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, requestId);
            logger.info(SQLLogger.logSQL(sql, requestId));

            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    return getDetailObject(rs);
                }
            }
        }
        return null;
    }

    @Override
    public void insertRequestInfo(Request request) throws SQLException {
        String sql;
        sql = "insert into approval.request_flow set"
                + " Frequest_name = ?, Frequest_templet = ?, Fcompany_id = ?, Fapplicant = ?, Fall_judger = ?,"
                + " Fwho_the_next = ?, Fcc = ?, Frequest_id = ?, Fversion = ?, Fouter_position = ?,"
                + " Frefuse_chain = ?, Frequest_template_type = ?, Frela_people = ?, Frequest_content_last = ?, Frequest_ancestor_id = ?,"
                + " Frequest_content_last_total_second = ?, Fnatural_content_time_last = ?, Fis_annual_leave = ?, Fapplicant_ciid = ?, Fall_judger_ciid = ?,"
                + " Fwho_the_next_ciid = ?, Fcc_ciid = ?, Frela_people_ciid = ?,Fapproval_num = ?, Ftotal_money = ?,"
                + " Fgeneration = ?, Fcontact_submit_time = ?, Fcontent_nums = ?, Fcontent_ids = ?, Fproxy_contact_id = ?,"
                + " Ffinished = 0, Fcreate_time = now(), Flatest_approved_time = now(), Fversion_info = ?";
        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setString(1, request.getTemplateName());
            ps.setInt(2, request.getTemplateId());
            ps.setInt(3, request.getCompanyId());
            ps.setInt(4, request.getApplicant());
            ps.setString(5, request.getAllJudger());

            ps.setString(6, request.getWhoTheNext());
            ps.setString(7, request.getCc());
            ps.setInt(8, request.getRequestDkey());
            ps.setInt(9, request.getVersion());
            ps.setString(10, request.getOuterPosition());

            ps.setString(11, request.getRefuseChain());
            ps.setInt(12, request.getDefaultType());
            ps.setString(13, request.getRelaPeople());
            ps.setString(14, request.getRequestContentLastStr());
            int requestAncestorId = Utils.isNull(request.getRequestAncestorId()) ? 0 : request.getRequestAncestorId();
            ps.setInt(15, requestAncestorId);

            ps.setString(16, request.getRequestContentLastTotalSecond());
            ps.setString(17, request.getNaturalContentTimeLast());
            int isAnnualLeave = Utils.isNull(request.getIsAnnualLeave()) ? 0 : request.getIsAnnualLeave();
            ps.setInt(18, isAnnualLeave);
            ps.setInt(19, request.getApplicantCiid());
            ps.setString(20, request.getAllJudgerCiid());

            ps.setString(21, request.getWhoTheNextCiid());
            ps.setString(22, request.getCcCiid());
            ps.setString(23, request.getRelaPeopleCiid());
            ps.setString(24, request.getApprovalNum());
            ps.setString(25, request.getTotalMoney());

            int templateGeneration = Utils.isNull(request.getTemplateGeneration()) ? 0 : request.getTemplateGeneration();
            ps.setInt(26, templateGeneration);
            ps.setString(27, request.getContactIdSubmitTime());
            ps.setString(28, request.getContentNums());
            ps.setString(29, request.getContentIds());
            ps.setObject(30, request.getProxyContactId());

            String versionInfo = getVersionInfo(request);
            ps.setString(31, versionInfo);

            logger.info(Formatter.aformat(sql, request.getTemplateName(), request.getTemplateId(),
                    request.getCompanyId(), request.getApplicant(), request.getAllJudger(), request.getWhoTheNext(),
                    request.getCc(), request.getRequestDkey(), request.getVersion(), request.getOuterPosition(), request.getRefuseChain(),
                    request.getDefaultType(), request.getRelaPeople(), request.getRequestContentLastStr(), requestAncestorId,
                    request.getRequestContentLastTotalSecond(), request.getNaturalContentTimeLast(),
                    isAnnualLeave, request.getApplicantCiid(), request.getAllJudgerCiid(), request.getWhoTheNextCiid(),
                    request.getCcCiid(), request.getRelaPeopleCiid(), request.getApprovalNum(), request.getTotalMoney(), templateGeneration,
                    request.getContactIdSubmitTime(), request.getContentNums(), request.getContentIds(), request.getProxyContactId(), versionInfo));

            ps.executeUpdate();
        }
    }

    private String getVersionInfo(Request request) {
        VersionInfo versionInfo = request.getVersionInfo();
        if (Utils.isNull(versionInfo)) {
            return null;
        }
        versionInfo.setVersionType(null);
        String str = JSONObject.toJSONString(versionInfo);
        if (str.length() >= 64) {
            return "版本信息异常";
        }
        return str;
    }

    @Override
    public void updateRequestCcInfo(Request request) throws SQLException {
        String sql = "update request_flow set Fcc = ?, Fcc_ciid = ?, Ffinished = ?, Ffinally_cc_time = ?, Ffinancial_status = ?"
                + " where Frequest_id = ? limit 1";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setString(1, request.getCc());
            ps.setString(2, request.getCcCiid());
            ps.setInt(3, request.getFinished());
            ps.setString(4, request.getFinallyCcTime());
            ps.setInt(5, request.getFinancialStatus());

            ps.setInt(6, request.getRequestDkey());

            logger.info(SQLLogger.logSQL(sql, request.getCc(), request.getCcCiid(), request.getFinished(), request.getFinallyCcTime(), request.getFinancialStatus(),
                    request.getRequestDkey()));

            ps.executeUpdate();
        }
    }

    @Override
    public void updateRequestCcBatch(List<Request> requests) throws SQLException {
        String sql = "update request_flow set Fcc = ?, Fcc_ciid = ?, Ffinished = ?, Ffinally_cc_time = ?, Ffinancial_status = ?"
                + " where Frequest_id = ? limit 1";
        try (PreparedStatement ps = getPreparedStatement(sql)) {
            for (Request request : requests) {
                ps.setString(1, request.getCc());
                ps.setString(2, request.getCcCiid());
                ps.setInt(3, request.getFinished());
                ps.setString(4, request.getFinallyCcTime());
                ps.setInt(5, request.getFinancialStatus());

                ps.setInt(6, request.getRequestDkey());

                logger.info(SQLLogger.logSQL(sql, request.getCc(), request.getCcCiid(), request.getFinished(), request.getFinallyCcTime(),
                        request.getFinancialStatus(), request.getRequestDkey()));
                ps.addBatch();
            }
            ps.executeBatch();
        }
    }

    @Override
    public void updateRequestPauseLeaveInfo(Request request) throws SQLException {
        String sql = "update request_flow"
                + " set Frequest_content_last_total_second = ?, Fnatural_content_time_last = ?, Fmanual_ending_time = ?"
                + " where Frequest_id = ? and Fcompany_id = ? limit 1";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            String manualEndingTime = getTimeStr(request.getManualEndingTime());
            ps.setString(1, request.getRequestContentLastTotalSecond());
            ps.setString(2, request.getNaturalContentTimeLast());
            ps.setString(3, manualEndingTime);
            ps.setInt(4, request.getRequestDkey());
            ps.setInt(5, request.getCompanyId());

            logger.info(SQLLogger.logSQL(sql, request.getRequestContentLastTotalSecond(), request.getNaturalContentTimeLast(),
                    manualEndingTime, request.getRequestDkey(), request.getCompanyId()));

            ps.executeUpdate();
        }
    }

    @Override
    public void updateBatchRequestLeaveInfo(List<Request> requests) throws SQLException {
        String sql = "update request_flow"
                + " set Frequest_content_last_total_second = ?, Fnatural_content_time_last = ?"
                + " where Frequest_id = ? and Fcompany_id = ? limit 1";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            for (Request request : requests) {
                ps.setString(1, request.getRequestContentLastTotalSecond());
                ps.setString(2, request.getNaturalContentTimeLast());
                ps.setInt(3, request.getRequestDkey());
                ps.setInt(4, request.getCompanyId());
                logger.info(SQLLogger.logSQL(sql, request.getRequestContentLastTotalSecond(), request.getNaturalContentTimeLast(),
                        request.getRequestDkey(), request.getCompanyId()));

                ps.addBatch();
            }

            ps.executeBatch();
        }
    }

    @Override
    public List<Request> selectTemplateRequestDetailByDateScope(Integer templateId, String startDate, String endDate) throws SQLException {
        List<Request> list = new ArrayList<>();
        String sql = getDetailQuerySql("where Frequest_templet = ? and date_format(Fcreate_time,'%Y-%m-%d') >= ?"
                + " and date_format(Fcreate_time,'%Y-%m-%d') <= ? and Ffinished != -99 and Fis_resubmit = 0");
        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, templateId);
            ps.setString(2, startDate);
            ps.setString(3, endDate);
            logger.info(SQLLogger.logSQL(sql, templateId, startDate, endDate));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(getDetailObject(rs));
                }
            }
        }
        return list;
    }

    @Override
    public List<Request> selectUserTemplateRequestDetailByDateScope(Integer companyInfoId, Integer templateId, String startDate, String endDate) throws SQLException {
        List<Request> list = new ArrayList<>();
        String sql = getDetailQuerySql("where Fapplicant_ciid = ? and Frequest_templet = ? and date_format(Fcreate_time,'%Y-%m-%d') >= ?"
                + " and date_format(Fcreate_time,'%Y-%m-%d') <= ? and Ffinished in(-2,1,2,3) and Fis_resubmit = 0 order by Ffinished");
        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyInfoId);
            ps.setInt(2, templateId);
            ps.setString(3, startDate);
            ps.setString(4, endDate);
            logger.info(SQLLogger.logSQL(sql, companyInfoId, templateId, startDate, endDate));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(getDetailObject(rs));
                }
            }
        }
        return list;
    }

    @Override
    public List<Request> selectNewestRequestByIds(Integer companyId, String requestIds) throws SQLException {
        List<Request> list = new ArrayList<>();
        if (StringUtils.isEmpty(requestIds)) {
            return list;
        }
        String sql = getQuerySql("where Fcompany_id = ? and Frequest_ancestor_id in("
                + " select Frequest_ancestor_id from request_flow where Frequest_id in(" + requestIds + ") "
                + ") order by Frequest_id");
        Map<Integer, Request> filterMap = new HashMap<>();
        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            logger.info(SQLLogger.logSQL(sql, companyId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Request request = getObject(rs);
                    filterMap.put(request.getRequestAncestorId(), request);
                }
            }
        }
        list.addAll(filterMap.values());
        return list;
    }

    @Override
    public void updateRequestOperation(Request request) throws SQLException {
        String sql = "update request_flow"
                + " set Frequest_step = ?, Ffinished = ?, Fcall_back_time = ?, Fdisable_time = ?, Flatest_approved_time = ?,"
                + " Foperator_id = ?, Ffinancial_status = ?"
                + " where Frequest_id = ? and Fcompany_id = ? limit 1";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, request.getRequestStep());
            ps.setInt(2, request.getFinished());
            ps.setString(3, request.getCallBackTime());
            ps.setString(4, request.getDisableTime());
            ps.setString(5, request.getLatestApprovedTime());

            ps.setInt(6, request.getOperatorId());
            ps.setInt(7, request.getFinancialStatus());

            ps.setInt(8, request.getRequestDkey());
            ps.setInt(9, request.getCompanyId());
            logger.info(SQLLogger.logSQL(sql, request.getRequestStep(), request.getFinished(), request.getCallBackTime(), request.getDisableTime(),
                    request.getLatestApprovedTime(), request.getOperatorId(), request.getFinancialStatus(), request.getRequestDkey(), request.getCompanyId()));

            ps.executeUpdate();
        }
    }

    @Override
    public List<Integer> selectRequestIdsByAncestorId(Integer id) throws SQLException {
        List<Integer> list = new ArrayList<>();
        String sql = "select Frequest_id from request_flow where (Frequest_id = ? or Frequest_ancestor_id =?)";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, id);
            ps.setInt(2, id);
            logger.info(SQLLogger.logSQL(sql, id, id));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(rs.getInt(1));
                }
            }
        }
        return list;
    }

    @Override
    public List<Request> selectUserRequestByTemplateIds(Integer companyId, Integer contactId, String month, String templateIds) throws SQLException {
        List<Request> list = new ArrayList<>();
        if (StringUtils.isEmpty(templateIds)) {
            return list;
        }
        String sql = getDetailQuerySql("where Fcompany_id = ? and Fapplicant = ? and date_format(Fcreate_time,'%Y-%m') = ?"
                + " and Frequest_templet in(" + templateIds + ") and Ffinished in(1,2) and Fis_resubmit = 0");
        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            ps.setInt(2, contactId);
            ps.setString(3, month);
            logger.info(SQLLogger.logSQL(sql, companyId, contactId, month));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(getDetailObject(rs));
                }
            }
        }
        return list;
    }

    @Override
    public List<Integer> selectUsualTemplateAncestorIds(Integer companyId, Integer companyInfoId) throws SQLException {
        List<Integer> list = new ArrayList<>();
        String sql = "select Fancestor_id from request_flow f"
                + " INNER JOIN request_template t on f.Frequest_templet = t.Ftemplate_id"
                + " where f.Fcompany_id = ? and f.Fapplicant_ciid = ? and t.Fstatus = 1 order by f.Frequest_id desc  limit 30";

        try (PreparedStatement ps = getPreparedStatement(sql)) {
            ps.setInt(1, companyId);
            ps.setInt(2, companyInfoId);
            logger.info(SQLLogger.logSQL(sql, companyId, companyInfoId));

            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    list.add(rs.getInt(1));
                }
            }
        }
        return list;
    }

    /**
     * 对前端参数多样化进行统一
     *
     * @param query
     */
    private void mergeStatus(Request query) {
        if (StringUtils.isBlank(query.getFinishedSequence())) {
            return;
        }
        if (StringUtils.isBlank(query.getCurrentStatus())) {
            query.setCurrentStatus(query.getFinishedSequence());
        } else {
            Set<Integer> set = new TreeSet<>();
            set.addAll(Utils.string2list(query.getCurrentStatus()));
            set.addAll(Utils.string2list(query.getFinishedSequence()));
            query.setCurrentStatus(Utils.collection2string(set));
        }
    }
}
